-- =============================================
-- Create database template
-- =============================================
USE master
GO

-- Drop the database if it already exists
IF  EXISTS (
	SELECT name 
		FROM sys.databases 
		WHERE name = N'SBM'
)
DROP DATABASE SBM
GO

CREATE DATABASE SBM --Saving book manager
GO
--set date format
set dateformat dmy
GO
-- =========================================
-- Create table SAVINGTYPE
-- =========================================
USE SBM
GO

IF OBJECT_ID('dbo.SAVINGTYPE', 'U') IS NOT NULL
  DROP TABLE dbo.SAVINGTYPE
GO

CREATE TABLE dbo.SAVINGTYPE
(
	TypeID			char(10)		NOT NULL, 
	TypeName		nvarchar(30)	NULL, 
	Term			int				NULL, --So thang
	InterestRate	float			NULL,
	AllowSave		bit				NULL,
	AllowWithdraw	bit				NULL,
	CONSTRAINT PK_ST PRIMARY KEY (TypeID)
)
GO

-- =========================================
-- Create table CUSTOMER
-- =========================================
USE SBM
GO

IF OBJECT_ID('dbo.CUSTOMER', 'U') IS NOT NULL
  DROP TABLE dbo.CUSTOMER
GO

CREATE TABLE dbo.CUSTOMER
(
	CustomerID	char(10)		NOT NULL, 
	FullName	nvarchar(30)	NULL,
	IDNumber	nvarchar(12)	NULL,
	HomeAddress	nvarchar(50)	NULL, 
	Email		nvarchar(35)	NULL,
	Phone		nvarchar(11)	NULL,
    CONSTRAINT PK_CT PRIMARY KEY (CustomerID)
)
GO


-- =========================================
-- Create table SAVINGBOOK
-- =========================================
USE SBM
GO

IF OBJECT_ID('dbo.SAVINGBOOK', 'U') IS NOT NULL
  DROP TABLE dbo.SAVINGBOOK
GO

CREATE TABLE dbo.SAVINGBOOK
(
	BookID					char(10)	NOT NULL, 
	CustomerID				char(10)	NULL, 
	TypeID					char(10)	NULL,
	OpeningDate				datetime	NULL,
	InterestStartingDate	datetime	NULL,
	AccountBalance			bigint		NULL,
	SBStatus				bit			NULL,
    CONSTRAINT PK_SB PRIMARY KEY (BookID)
)
GO
-- =========================================
-- Create table DEPOSIT
-- =========================================
USE SBM
GO

IF OBJECT_ID('dbo.DEPOSIT', 'U') IS NOT NULL
  DROP TABLE dbo.DEPOSIT
GO

CREATE TABLE dbo.DEPOSIT
(
	DepositID		char(10)		NOT NULL, 
	DepositingDate	datetime		NULL,
	CustomerName	nvarchar(30)	NULL,
	BeneficiaryName	nvarchar(30)	NULL,
	BookID			char(10)		NULL,
	AmountOfMoney	int				NULL, 
    CONSTRAINT PK_DO PRIMARY KEY (DepositID)
)
GO

-- =========================================
-- Create table WITHDRAW
-- =========================================
USE SBM
GO

IF OBJECT_ID('dbo.WITHDRAW', 'U') IS NOT NULL
  DROP TABLE dbo.WITHDRAW
GO

CREATE TABLE dbo.WITHDRAW
(
	WithdrawID		char(10)		NOT NULL, 
	WithdrawingDate	datetime		NULL,
	BookID			char(10)		NULL, 
	CustomerName	nvarchar(30)	NULL, 
	IDNumber		nvarchar(12)	NULL,
	AmountOfMoney	int				NULL,
    CONSTRAINT PK_WO PRIMARY KEY (WithdrawID)
)
GO

-- =========================================
-- Create table DAILYREPORT
-- =========================================
USE SBM
GO

IF OBJECT_ID('dbo.DAILYREPORT', 'U') IS NOT NULL
  DROP TABLE dbo.DAILYREPORT
GO

CREATE TABLE dbo.DAILYREPORT
(
	ReportID		char(10)	NOT NULL, 
	ReportingDate	datetime	NULL,  
    CONSTRAINT PK_DR PRIMARY KEY (ReportID)
)
GO

-- =========================================
-- Create table DAILYREPORTDETAIL
-- =========================================
USE SBM
GO

IF OBJECT_ID('dbo.DAILYREPORTDETAIL', 'U') IS NOT NULL
  DROP TABLE dbo.DAILYREPORTDETAIL
GO

CREATE TABLE dbo.DAILYREPORTDETAIL
(
	DetailID		char(10)	NOT NULL, 
	ReportID		char(10)	NULL, 
	SavingTypeID	char(10)	NULL, 
	TotalOfRevenues	int			NULL,
	TotalOfCost		int			NULL,
    CONSTRAINT PK_DRD PRIMARY KEY (DetailID)
)
GO

-- =========================================
-- Create table MONTHLYREPORT
-- =========================================
USE SBM
GO

IF OBJECT_ID('dbo.MONTHLYREPORT', 'U') IS NOT NULL
  DROP TABLE dbo.MONTHLYREPORT
GO

CREATE TABLE dbo.MONTHLYREPORT
(
	ReportID		char(10)	NOT NULL, 
	ReportingMonth	datetime	NULL, 
	SavingTypeID	char(10)	NULL, 
    CONSTRAINT PK_MR PRIMARY KEY (ReportID)
)
GO

-- =========================================
-- Create table MONTHLYREPORTDETAIL
-- =========================================
USE SBM
GO

IF OBJECT_ID('dbo.MONTHLYREPORTDETAIL', 'U') IS NOT NULL
  DROP TABLE dbo.MONTHLYREPORTDETAIL
GO

CREATE TABLE dbo.MONTHLYREPORTDETAIL
(
	DetailID			char(10)	NOT NULL, 
	ReportID			char(10)	NULL, 
	ReportingDate		datetime	NULL, 
	TotalOfOpen			int			NULL,
	TotalOfClose		int			NULL,
    CONSTRAINT PK_MRD PRIMARY KEY (DetailID)
)
GO

-- =========================================
-- Create table INDEX
-- =========================================
USE SBM
GO

IF OBJECT_ID('dbo.PARAMETER', 'U') IS NOT NULL
  DROP TABLE dbo.PARAMETER
GO

CREATE TABLE dbo.PARAMETER
(
	MinOfDeposit		int	NULL,
	MinOfWithdraw		int	NULL,
	MinOfDepositPeriod	int NULL,
	InterestFreePeriod	int NULL
)
GO

-- =========================================
-- Create table PERMISSION
-- =========================================
USE SBM
GO

IF OBJECT_ID('dbo.PERMISSION', 'U') IS NOT NULL
  DROP TABLE dbo.PERMISSION
GO

CREATE TABLE dbo.PERMISSION
(
	PermissionID	char(10)		NOT NULL,
	PermissionName	nvarchar(30)	NULL,
	Permission		bit				NULL,
	DepositWithdraw	bit				NULL,
	Report			bit				NULL,
	Search			bit				NULL,
	DeleteInfo		bit				NULL
	CONSTRAINT PK_PM PRIMARY KEY(PermissionID)
	)
GO

-- =========================================
-- Create table USERLOGIN
-- =========================================
USE SBM
GO

IF OBJECT_ID('dbo.USELOGIN', 'U') IS NOT NULL
  DROP TABLE dbo.USERLOGIN
GO

CREATE TABLE dbo.USERLOGIN
(
	UserID			char(10)		NOT NULL,
	UserName		nvarchar(30)	NULL,
	UserPassword	nvarchar(30)	NULL,
	PermissionID	char(10)		NULL,
	CONSTRAINT PK_UL PRIMARY KEY(UserID)
	)
GO

-- =========================================
-- Add FOREIGN KEY for tables
-- =========================================

USE SBM
GO

-- Add a new FOREIGN KEY to the table SAVINGBOOK references to table SAVINGTYPE
ALTER TABLE dbo.SAVINGBOOK
	ADD CONSTRAINT FK_SB_ST FOREIGN KEY (TypeID) REFERENCES	dbo.SAVINGTYPE(TypeID) ON DELETE CASCADE
GO

-- Add a new FOREIGN KEY to the table SAVINGBOOK references to table CUSTOMER
ALTER TABLE dbo.SAVINGBOOK
	ADD	CONSTRAINT FK_SB_CT FOREIGN KEY (CustomerID) REFERENCES	dbo.CUSTOMER(CustomerID) ON DELETE CASCADE
GO

-- Add a new FOREIGN KEY to the table DEPOSIT references to table SAVINGBOOK
ALTER TABLE dbo.DEPOSIT
	ADD CONSTRAINT FK_DO_SB FOREIGN KEY (BookID) REFERENCES dbo.SAVINGBOOK(BookID) ON DELETE SET NULL
GO

-- Add a new FOREIGN KEY to the table WITHDRAW references to table SAVINGBOOK
ALTER TABLE dbo.WITHDRAW
	ADD CONSTRAINT FK_WO_SB FOREIGN KEY (BookID) REFERENCES dbo.SAVINGBOOK(BookID) ON DELETE SET NULL
GO

-- Add a new FOREIGN KEY to the table DAILYREPORTDETAIL references to table SAVINGTYPE
ALTER TABLE dbo.DAILYREPORTDETAIL
	ADD CONSTRAINT FK_DRD_ST FOREIGN KEY(SavingTypeID) REFERENCES dbo.SAVINGTYPE(TypeID) ON DELETE CASCADE
GO

-- Add a new FOREIGN KEY to the table DAILYREPORTDETAIL references to table DAILYREPORT
ALTER TABLE dbo.DAILYREPORTDETAIL
	ADD	CONSTRAINT FK_DRD_DR FOREIGN KEY(ReportID) REFERENCES dbo.DAILYREPORT(ReportID) ON DELETE CASCADE
GO

-- Add a new FOREIGN KEY to the table MONTHLYREPORT references to table SAVINGTYPE
ALTER TABLE dbo.MONTHLYREPORT
	ADD CONSTRAINT FK_MR_ST FOREIGN KEY(SavingTypeID) REFERENCES dbo.SAVINGTYPE(TypeID) ON DELETE CASCADE
GO

-- Add a new FOREIGN KEY to the table MONTHLYREPORTDETAIL references to table MONTHLYREPORT
ALTER TABLE dbo.MONTHLYREPORTDETAIL
	ADD CONSTRAINT FK_MRD_MR FOREIGN KEY (ReportID) REFERENCES dbo.MONTHLYREPORT(ReportID) ON DELETE CASCADE
GO

-- Add a new FOREIGN KEY to the table USERLOGIN references to table PERMISSION
ALTER TABLE dbo.USERLOGIN
	ADD CONSTRAINT FK_UL_PM FOREIGN KEY (PermissionID) REFERENCES dbo.PERMISSION(PermissionID) ON DELETE CASCADE
GO
